package com.example.zsydb.repository;

import com.example.zsydb.model.CalculationResult;
import com.example.zsydb.service.ScheduledTaskService;
import com.zaxxer.hikari.HikariDataSource;
import com.zaxxer.hikari.HikariPoolMXBean;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.dao.DataAccessException;
import org.springframework.dao.DataAccessResourceFailureException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.transaction.annotation.Transactional;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.*;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.support.TransactionSynchronizationManager;

import javax.sql.DataSource;

@Repository
public class ResultRepository {

    private static final Logger logger = LoggerFactory.getLogger(ResultRepository.class);
//    @Autowired
//    @Qualifier("primaryJdbcTemplate") // 指定使用主数据源的JdbcTemplate
//    private JdbcTemplate jdbcTemplate;

    private final JdbcTemplate resultJdbcTemplate;

    public ResultRepository(@Qualifier("resultJdbcTemplate") JdbcTemplate resultJdbcTemplate) {
        this.resultJdbcTemplate = resultJdbcTemplate;
    }



    public void saveResult(String jh, String gkzd, String dym, String cylGtmj, String id,
                           Double stroke, Double frequency, Double suspMaxLoad, Double suspMinLoad,
                           Double upgoingMaxCurrent, Double downgoingMaxCurrent, Double gggf, Double ggcd,
                           Double cyggf1, Double cygcd1, Double cyggf2, Double cygcd2,
                           Double yy, Double ty, Double hs, Double bj, Double bs,
                           String dynaCreateTime, String checkDate) {
        String sql = "INSERT INTO calculation_result(gtly_guid, jh, gkzd, dym, " +
                "cyl_gtmj, id, stroke, FREQUENCY, SUSP_MAX_LOAD, SUSP_MIN_LOAD, UPGOING_MAX_CURRENT, " +
                "DOWNGOING_MAX_CURRENT, GGGF, " +
                "GGCD, CYGGF1, CYGCD1, CYGGF2, CYGCD2, YY, TY, HS, BJ, " +
                "BS, csrq, DYNA_CREATE_TIME, CHECK_DATE) "
                + "VALUES(SYS_GUID(), ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
                "?, ?, ?, ?, SYSTIMESTAMP, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'))";
        resultJdbcTemplate.update(sql, jh, gkzd, dym, cylGtmj, id, stroke, frequency, suspMaxLoad,
                suspMinLoad, upgoingMaxCurrent, downgoingMaxCurrent, gggf, ggcd, cyggf1,
                cygcd1, cyggf2, cygcd2, yy, ty, hs, bj, bs, dynaCreateTime, checkDate);
    }

//    public void saveResult(){
//        resultJdbcTemplate.update("BEGIN INSERT_GSFW_GTLY_RESULT(); END;");
//    }

    // 添加事务注解
    @Transactional
//    public void saveResult() {
//        // 使用标准存储过程调用语法
//        resultJdbcTemplate.update("{call INSERT_GSFW_GTLY_RESULT()}");
//    }

    public void saveResult() {
        final int maxRetries = 2; // 最大重试次数（不含首次调用）
        final long initialDelay = 60_000; // 初始延迟60秒（1分钟）
        long delay = initialDelay;

        for (int attempt = 0; attempt <= maxRetries; attempt++) {
            try {
                resultJdbcTemplate.update("{call INSERT_GSFW_GTLY_RESULT()}");
                return; // 执行成功则直接返回
            } catch (DataAccessException e) {
                if (attempt < maxRetries) {
                    // 计算实际等待时间（指数退避）
                    long waitTime = (long)(initialDelay * Math.pow(1, attempt));

                    // 记录警告日志（包含重试信息）
                    logger.warn("存储过程调用失败，将在 {} 秒后重试。已尝试次数: {}/{}。错误原因: {}",
                            waitTime/1000, attempt + 1, maxRetries, e.getMessage());

                    try {
                        Thread.sleep(waitTime); // 线程休眠等待
                    } catch (InterruptedException ie) {
                        Thread.currentThread().interrupt(); // 恢复中断状态
                        throw new DataAccessResourceFailureException("重试过程被中断", ie);
                    }
                } else {
                    // 达到最大重试次数后记录错误并抛出原始异常
                    logger.error("存储过程调用失败已达最大重试次数（{}次），终止操作。错误详情：", maxRetries + 1, e);
                    throw e;
                }
            }
        }
    }

    public void clearResultTable() {
        String sql = "TRUNCATE TABLE calculation_result";
        resultJdbcTemplate.update(sql);
    }

//    @Transactional(timeout = 120)
//    public void saveResult() {
//        logger.info("开始执行存储过程");
//        try {
//            resultJdbcTemplate.update("{call INSERT_GSFW_GTLY_RESULT()}");
//            logger.info("存储过程执行完成");
//        } catch (DataAccessException e) {
//            throw new org.springframework.dao.DataAccessResourceFailureException(
//                    "存储过程执行失败: " + e.getCause().getMessage(),
//                    e
//            );
//        }
//    }



//批量插入
// 首先在类中定义静态映射表
private static final Map<String, Integer> GKZD_DM_MAP = new HashMap<>();
    static {
        GKZD_DM_MAP.put("筛管堵", 84);
        GKZD_DM_MAP.put("正常", 11);
        GKZD_DM_MAP.put("连抽带喷", 21);
        GKZD_DM_MAP.put("供液不足", 31);
        GKZD_DM_MAP.put("井下故障影响", 4);
        GKZD_DM_MAP.put("气影响", 41);
        GKZD_DM_MAP.put("气锁", 42);
        GKZD_DM_MAP.put("蜡影响", 43);
        GKZD_DM_MAP.put("砂影响", 44);
        GKZD_DM_MAP.put("稠油影响", 45);
        GKZD_DM_MAP.put("震动影响", 46);
        GKZD_DM_MAP.put("漏失", 5);
        GKZD_DM_MAP.put("泵漏失", 51);
        GKZD_DM_MAP.put("游动凡尔漏", 52);
        GKZD_DM_MAP.put("固定凡尔漏", 53);
        GKZD_DM_MAP.put("油管漏", 54);
        GKZD_DM_MAP.put("卡撞乱", 6);
        GKZD_DM_MAP.put("游动凡尔卡死", 61);
        GKZD_DM_MAP.put("固定凡尔卡死", 62);
        GKZD_DM_MAP.put("活塞脱出工作筒", 63);
        GKZD_DM_MAP.put("活塞撞固定凡尔", 64);
        GKZD_DM_MAP.put("衬套乱", 65);
        GKZD_DM_MAP.put("断脱", 7);
        GKZD_DM_MAP.put("抽油杆断", 71);
        GKZD_DM_MAP.put("抽油杆脱", 72);
        GKZD_DM_MAP.put("其他解释", 83);
        GKZD_DM_MAP.put("双凡尔漏", 55);
        GKZD_DM_MAP.put("碰泵", 47);
        GKZD_DM_MAP.put("油管断", 73);
        GKZD_DM_MAP.put("惯性影响", 81);
        GKZD_DM_MAP.put("未测", 99);
        GKZD_DM_MAP.put("脱节器脱", 74);
    }

    public void saveBatchResults(List<CalculationResult> results) {
        String batchTimestamp = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        // 修改SQL语句，添加GKZD_DM字段
        String sql = "INSERT INTO calculation_result(" +
                "gtly_guid, jh, gkzd, GKZD_DM, dym, cyl_gtmj, id, stroke, FREQUENCY, SUSP_MAX_LOAD, " +
                "SUSP_MIN_LOAD, UPGOING_MAX_CURRENT, DOWNGOING_MAX_CURRENT,DISPLACEMENT," +
                "DISP_LOAD,DISP_CURRENT,ACTIVE_POWER, GGGF, GGCD, " +
                "CYGGF1, CYGCD1, CYGGF2, CYGCD2, YY, TY, HS, BJ, BS, csrq, DYNA_CREATE_TIME, CHECK_DATE) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, SYSTIMESTAMP, TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'), TO_DATE(?, 'YYYY-MM-DD HH24:MI:SS'))";

        resultJdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                CalculationResult r = results.get(i);
                String guid = batchTimestamp + "_" + (i + 1);
                ps.setString(1, guid);
                ps.setString(2, r.getJh());
                ps.setString(3, r.getGkzd());

                // 设置GKZD_DM字段值
                Integer gkzdDm = GKZD_DM_MAP.get(r.getGkzd());
                ps.setObject(4, gkzdDm != null ? gkzdDm : 0);  // 如果未找到映射，默认设为0

                ps.setString(5, r.getDym());
                ps.setString(6, r.getCylGtmj());
                ps.setString(7, r.getId());
                ps.setObject(8, r.getStroke());
                ps.setObject(9, r.getFrequency());
                ps.setObject(10, r.getSuspMaxLoad());
                ps.setObject(11, r.getSuspMinLoad());
                ps.setObject(12, r.getUpgoingMaxCurrent());
                ps.setObject(13, r.getDowngoingMaxCurrent());

                ps.setString(14, r.getDisplacenent());
                ps.setString(15, r.getDispLoad());
                ps.setString(16, r.getDispCurrent());
                ps.setString(17, r.getActivePower());

                ps.setObject(18, r.getGggf());
                ps.setObject(19, r.getGgcd());
                ps.setObject(20, r.getCyggf1());
                ps.setObject(21, r.getCygcd1());
                ps.setObject(22, r.getCyggf2());
                ps.setObject(23, r.getCygcd2());
                ps.setObject(24, r.getYy());
                ps.setObject(25, r.getTy());
                ps.setObject(26, r.getHs());
                ps.setObject(27, r.getBj());
                ps.setObject(28, r.getBs());
                ps.setString(29, r.getDynaCreateTime());
                ps.setString(30, r.getCheckDate());
            }

            @Override
            public int getBatchSize() {
                return results.size();
            }
        });
    }

    // 新增分页查询方法
    public List<Map<String, Object>> findResults(int page, int size) {
        String sql = "SELECT * FROM (" +
                "   SELECT t.*, ROWNUM rn FROM (" +
                "       SELECT gtly_guid, jh, dym, cyl_gtmj, gkzd, id, csrq " +
                "       FROM calculation_result " +
                "       ORDER BY csrq DESC" +
                "   ) t WHERE ROWNUM <= ?" +
                ") WHERE rn > ?";

        int start = (page - 1) * size;
        int end = page * size;
        return resultJdbcTemplate.queryForList(sql, end, start);
    }

}